<?php
namespace App\Repositories;

use App\Exceptions\BasicException;
use App\Models\MainDB\GiveGift;
use App\Models\MainDB\GiveGiftDay;
use App\Models\MainDB\UserData;
use App\Models\MainDB\UserRoom;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use function Matrix\add;    //引入hash

class RoomAccountRepository
{
    public function statistic_day() {
//        $today = date('Y-m-d');
//        DB::connection('mysql')->delete('delete from ln_give_gift_day where date = "' . $today . '"');
//        DB::connection('mysql')->insert('insert into ln_give_gift_day (room_id,date,total,week,month) select room_id,"' . $today . '" as date,sum(total_price) total,"' . date('Y-W') . '" as week,"' . date('Y-m') . '" as month from ln_give_gift where room_id > 0 and add_time between ' . strtotime($today) . ' and ' . time() . ' group by room_id');
        $today = date('Y-m-d');
        $week = date('o-W');
        $month = date('Y-m');
        $data = app('model_give_gift')
            ->select('room_id', DB::raw('sum(total_price) as total'))
            ->where('room_id', '>', 0)
            ->whereBetween('add_time', [strtotime($today), time()])
            ->groupBy('room_id')
            ->get()->toArray();
        $idArray = app('model_give_gift_day')->select('id', 'date', 'room_id')->where('date', $today)->get()->keyBy('room_id')->toArray();
        foreach ($data as $key => &$value) {
            $value['date'] = $today;
            $value['week'] = $week;
            $value['month'] = $month;
            $value['id'] = array_key_exists($value['room_id'], $idArray) ? $idArray[$value['room_id']]['id'] : 0;
            if ($value['id'] > 0) {
                app('model_give_gift_day')->where('id', $value['id'])->update($value);
            } else {
                unset($value['id']);
                app('model_give_gift_day')->insert($value);
            }
        }
    }

    public function getQueryList($params = []) {
        $this->statistic_day();

        $type = $params['type'];
        $where = [];
        $datemin = isset($params['date_start']) ? $params['date_start'] : "";
        $datemax = isset($params['date_end']) ? $params['date_end'] : "";
        switch ($type) {
            case 2:
                $group_field = "a.week as group_time";
                $group = "a.week";
                if ($datemin) {
                    $time_value = date('o-W', strtotime($datemin));
                    $where[] = ['a.week', "=", $time_value];
                }
                break;
            case 3:
                $group_field = "a.month as group_time";
                $group = "a.month";
                if ($datemin) {
                    $time_value = date('Y-m', strtotime($datemin));
                    $where[] = ['a.month', "=", $time_value];
                }
                break;
            default:
                $group_field = "a.date as group_time";
                $group = "a.date";
                //条件查询
                if (!empty($datemin) && !empty($datemax)) {
                    $where[] = ['a.date', ">=" , $datemin];
                    $where[] = ['a.date', "<=" , $datemax];
                } elseif (!empty($datemin)) {
                    $where[] = ['a.date', ">=" , $datemin];
                } elseif (!empty($datemax)) {
                    $where[] = ['a.date', "<=" , $datemax];
                }
        }

        $keyword = isset($params['keyword']) ? $params['keyword'] : '';
        if ($keyword) {
            if (is_numeric($keyword)) {
                $where[] = ['b.room_id', "=", $keyword];
            } else {
                $where[] = ['b.room_name', 'like', "%$keyword%"];
            }
        }

        $page = empty($params['page']) ? 1 : $params['page'];
        $perPage = empty($params['perPage']) ? 10 : $params['perPage'];

        $userRoomRes = new UserRoom();
        $userDataRes = new UserData();
        $GiveGiftDayRes = new GiveGiftDay();

        $result = DB::connection('mysql')
            ->table($GiveGiftDayRes->getTable() . " as a")
            ->select(["b.id",DB::raw("sum(a.total) as number"), $group_field, "b.room_id","b.room_name","c.user_code"])
            ->leftJoin(DB::raw($userRoomRes->getTable() . " as b"),'a.room_id', "=", 'b.id')
            ->leftJoin(DB::raw($userDataRes->getTable() . " as c"),'b.user_id', "=",'c.user_id')
            ->where($where)
            ->groupBy([$group,'a.room_id'])
            ->orderBy('group_time','desc')->orderBy('number','desc')->orderBy('a.room_id','asc')
            ->paginate($perPage, ['*'], '', $page);

        foreach ($result as $item) {
            $item->date = $item->group_time;
            $item->show_date = $item->group_time;
            if ($type == 2) {
                $item->show_date = $this->week2date($item->group_time);
            }
        }

        return $result;
    }

    private function week2date($yearWeek) {
        list($year, $week) = explode('-', $yearWeek);
        $first_day_of_year = mktime(0, 0, 0, 1, 1, $year);
        $first_week_day = date('N', $first_day_of_year);
        $first_week_number = date('W', $first_day_of_year);
        $format = 'Y/m/d';
        if ($first_week_number == 1) {
            $day = (1 - ($first_week_day - 1)) + 7 * ($week - 1);
            $start_date = date($format, mktime(0, 0, 0, 1, $day, $year));
            $end_date = date($format, mktime(0, 0, 0, 1, $day + 6, $year));
        } else {
            $day = (9 - $first_week_day) + 7 * ($week - 1);
            $start_date = date($format, mktime(0, 0, 0, 1, $day, $year));
            $end_date = date($format, mktime(0, 0, 0, 1, $day + 6, $year));
        }

        return $start_date . ' - ' . $end_date;
    }

    public function detail($params = []) {
        $date = $params['date'];
        $type = $params['type'];
        $id = empty($params['id']) ? 0 : $params['id'];
        switch ($type) {
            case 2:
                $group_field = DB::raw("FROM_UNIXTIME(a.add_time,'%x-%v')");
                break;
            case 3:
                $group_field = DB::raw("FROM_UNIXTIME(a.add_time,'%Y-%m')");
                break;
            default:
                $group_field = "FROM_UNIXTIME(a.add_time,'%Y-%m-%d')";
        }

        $whereFunc = function ($query) use ($type, $id,$date) {
            $query->whereRaw(DB::raw("b.id = $id"));
            $query->whereRaw(DB::raw("a.number > 0"));
            $keyword = empty($params['keyword']) ? "" : $params['keyword'];
            if ($keyword) {
                if (is_numeric($keyword)) {
                    $query->whereRaw(DB::raw("b.room_id = $keyword"));
                } else {
                    $query->whereRaw(DB::raw("b.room_name like %$keyword%"));
                }
            }
        };

        $GiveGiftRes = new GiveGift();
        $userRoomRes = new UserRoom();
        $userDataRes = new UserData();

        $page = empty($params['page']) ? 1 : $params['page'];
        $perPage = empty($params['perPage']) ? 100 : $params['perPage'];

        $subQuery = DB::connection('mysql')
            ->table($GiveGiftRes->getTable() . " as a")
            ->select([DB::raw("a.total_price number"), DB::raw("$group_field as date"),"a.id", "b.room_id","b.room_name","a.add_time","c.user_code"])
            ->leftJoin(DB::raw($userRoomRes->getTable() . " as b"),"a.room_id","=", "b.id")
            ->leftJoin(DB::raw($userDataRes->getTable() . " as c"),"b.user_id","=", "c.user_id")
            ->where($whereFunc);

        $result = DB::connection('mysql')->table(DB::raw("({$subQuery->toSql()}) as t1"))
            ->setBindings(["1", "2", "3"])
            ->whereRaw(DB::raw("t1.date = '$date'"))->orderBy('t1.id', 'desc')
            ->paginate($perPage, ['*'], '', $page);

        foreach ($result as $item) {
            $item->add_time = date("Y-m-d H:i:s",$item->add_time);
            // 展示全部的时间信息
            $item->date = $item->add_time;
        }

        return $result;
    }
}